﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;

namespace BLL
{
   public class ClsGiaTriCacThamSoPhuBus
    {
       public ClsGiaTriCacThamSoPhuInfo Info = new ClsGiaTriCacThamSoPhuInfo();
        public DataTable SelectMau()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"Select MaMau,TenMau FROM DuLieuMau";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectThamSo()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"Select MaThamSo,TeThamSo FROM ThamSo";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectAll()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT DuLieuMau.TenMau,ThamSo.TeThamSo,ThamSo.VietTat,ThamSoPhu.GiaTri,ThamSo.DonVi
                            FROM DuLieuMau,ThamSo,ThamSoPhu
                            WHERE ThamSoPhu.MaMau=DuLieuMau.MaMau
                            AND ThamSoPhu.MaThamSo=ThamSo.MaThamSo";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectOne()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT DuLieuMau.TenMau,ThamSo.TeThamSo,ThamSo.VietTat,ThamSoPhu.GiaTri,ThamSo.DonVi
                            FROM DuLieuMau,ThamSo,ThamSoPhu
                            WHERE ThamSoPhu.MaMau=DuLieuMau.MaMau
                            AND ThamSoPhu.MaThamSo=ThamSo.MaThamSo
                            AND DuLieuMau.MaMau like '%" + Info.MaMau + "%'";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertThamSoPhu '" + Info.MaMau + "','" + Info.MaThamSo + "','" + Info.GiaTri+ "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateThamSoPhu '" + Info.MaMau + "','" + Info.MaThamSo + "','" + Info.GiaTri+ "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
        public bool Xoa()
        {
            string strSql = "EXECUTE DeleteThamSoPhu'" + Info.MaMau + "','" + Info.MaThamSo + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }
    }
}
